package connect;

import bean.CompteBean;
import bean.LoginBean;
import bean.UtilisateurBean;
import entity.Statistique;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.EJB;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import mySqlObject.*;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import session.StatistiqueFacadeLocal;

/**
 *
 * @author Mély
 */
@ManagedBean
@SessionScoped
public class MySqlConnect {

    private String login;
    private String password;
    @EJB
    private StatistiqueFacadeLocal statistiqueBean;
    @ManagedProperty(value = "#{compteBean}")
    private CompteBean compteBean;
    @ManagedProperty(value = "#{loginBean}")
    private LoginBean loginBean;
    private Connection connection = null;
    private List<UserMySql> allUser;
    private String roleDeConnexion;
    private List<TableMySql> allTableMySqlForCurrentUser;
    private List<ViewMySql> allViewMySqlForCurrentUser;
    private List<MethodeMySql> allMethodeMySqlForCurrentUser;
    private List<TriggerMySql> allTriggerMySqlForCurrentUser;
    private List<ObjectMySql> allObjectMySqlForCurrentUser;
    private ViewMySql currentView;
    private MethodeMySql currentMethode;
    private TableMySql currentTable;
    private TriggerMySql currentTrigger;
    private String myQuery;
    private String backupHotPath;
    private String backupColdPath;
    private String installPath;
    private String nameService;
    
    public MySqlConnect() {
    }

    public void reset() {
        this.login = null;
        this.password = null;
        this.allUser = null;
        roleDeConnexion = null;
    }

    public String connect() {
        Statistique stat = new Statistique(this.getLoginBean().getUser().getUserid(), this.getCompteBean().getCurrent().getId(), new java.util.Date());
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String portNumber = String.valueOf(this.getCompteBean().getCurrent().getPortNumber());
            setConnection(DriverManager.getConnection("jdbc:mysql://"
                    + this.getCompteBean().getCurrent().getServer()
                    + ":"
                    + portNumber
                    + "/"
                    + this.getCompteBean().getCurrent().getName(), this.getLogin(), this.getPassword()));
            stat.setSuccess(Boolean.TRUE);
            statistiqueBean.create(stat);
            if (this.isAdmin()) {
                return "/ConnectionMySql/sqlConnectionAdministrateur.jsf?faces-redirect=true";
            } else if (this.isDeveloppeur()) {
                return "/ConnectionMySql/sqlConnectionDeveloppeur.jsf?faces-redirect=true";
            }
        } catch (SQLException ex) {
            Logger.getLogger(UtilisateurBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UtilisateurBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        stat.setSuccess(Boolean.FALSE);
        statistiqueBean.create(stat);
        //password = null;
        return "/ConnectionMySql/sqlConnectionError.jsf?faces-redirect=true";
    }

    /*
     * Déconnection à la base de données courante en passant par un clic sur la
     * page web
     */
    public String deconnectFromWeb() {
        reset();
        if (deconnect()) {
            return "/ConnectionMySql/sqlDisconnect.jsf?faces-redirect=true";
        }
        return "/ConnectionMySql/sqlDisconnectError.jsf?faces-redirect=true";
    }

    /**
     *
     * @return true if deconnect success, false if deconnect error
     */
    public boolean deconnect() {
        try {
            this.getConnection().close();
            this.connection = null;
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public String getLogin() {
        return login;
    }

    public void setLogin(String login) {
        this.login = login;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public CompteBean getCompteBean() {
        return compteBean;
    }

    public void setCompteBean(CompteBean compteBean) {
        this.compteBean = compteBean;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public String getRoleDeConnexion() {
        return roleDeConnexion;
    }

    public void setRoleDeConnexion(String roleDeConnexion) {
        this.roleDeConnexion = roleDeConnexion;
    }

    public boolean isAdmin() {
        if ("Administrateur".equals(this.getRoleDeConnexion())) {
            return true;
        }
        return false;
    }

    public boolean isDeveloppeur() {
        if ("Developpeur".equals(this.getRoleDeConnexion())) {
            return true;
        }
        return false;
    }

    /**
     *
     * REQUETES ADMINISTRATEUR
     */
    public List<UserMySql> getAllUser() {
        allUser = new ArrayList<UserMySql>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery("SELECT host, user, max_questions, max_updates, max_connections, max_user_connections, "
                    + "select_priv, insert_priv, update_priv, "
                    + "delete_priv, create_priv, drop_priv, "
                    + "reload_priv, shutdown_priv, process_priv, "
                    + "file_priv, grant_priv, references_priv, "
                    + "index_priv, alter_priv, show_db_priv, "
                    + "super_priv,create_tmp_table_priv, lock_tables_priv, "
                    + "execute_priv, repl_slave_priv, repl_client_priv, "
                    + "create_view_priv,show_view_priv, create_routine_priv, "
                    + "alter_routine_priv, create_user_priv, event_priv,trigger_priv, "
                    + "create_tablespace_priv FROM mysql.user;");
            while (res.next()) {
                UserMySql userMySql = new UserMySql();
                List<String> privileges = new ArrayList<String>();
                userMySql.setHost(res.getString(1));
                userMySql.setName(res.getString(2));
                userMySql.setMaxQuestions(res.getInt(3));
                userMySql.setMaxUpdates(res.getInt(4));
                userMySql.setMaxConnections(res.getInt(5));
                userMySql.setMaxUserConnections(res.getInt(6));
                
                ResultSetMetaData rsmd = res.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 7; i < columnCount + 1; i++ ) {
                    String name = rsmd.getColumnName(i);
                    if("Y".equals(res.getString(name))) {
                        privileges.add(name);
                    }
                }
                userMySql.setPrivileges(privileges);
                allUser.add(userMySql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allUser;
    }
    
    public List<TablespaceMySql> getAllTablespaces() {
        List<TablespaceMySql> tablespaces = new ArrayList<TablespaceMySql>();
        String query = "SELECT tablespace_name, engine, tablespace_type, "
                + "extent_size, autoextend_size, maximum_size FROM information_schema.tablespaces;";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                TablespaceMySql tablespace = new TablespaceMySql();
                tablespace.setName(res.getString(1));
                tablespace.setEngine(res.getString(2));
                tablespace.setType(res.getString(3));
                tablespace.setExtentSize(res.getString(4));
                tablespace.setAutoExtendSize(res.getString(5));
                tablespace.setMaximumSize(res.getString(6));
                tablespaces.add(tablespace);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tablespaces;
    }
    
    public List<Databases> getSizeByDatabase() {
        List<Databases> dbs = new ArrayList<Databases>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery("SELECT DISTINCT table_schema \"Databases\" "
                    + "FROM information_schema.TABLES;");
            while (res.next()) {
                Databases db = new Databases();
                db.setName(res.getString(1));
                dbs.add(db);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        for (Databases database : dbs) {
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery("SELECT SUM(data_length + index_length)/1024 "
                        + "FROM information_schema.TABLES "
                        + "WHERE TABLE_SCHEMA = '"
                        + database.getName()
                        + "';");
                while (res.next()) {
                    database.setSize(res.getDouble(1));
                }
            } catch (SQLException ex) {
                Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return dbs;        
    }
    
    public void saveBackupColdScript() {
        if(System.getProperty("file.separator").charAt(0) != (this.getBackupColdPath().charAt(this.getBackupColdPath().length()-1))) {
            String backslash= System.getProperty("file.separator");
            backupColdPath += backslash;            
        }
        
        if(System.getProperty("file.separator").charAt(0) != (this.getInstallPath().charAt(this.getInstallPath().length()-1))) {
            String backslash= System.getProperty("file.separator");
            installPath += backslash;            
        }
        
        //Recherche où se trouve le répertoire DATA
        String query = "SHOW variables WHERE variable_name='datadir';";
        String dataDir = "";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                dataDir = res.getString(2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        String command = "\""
                + this.getInstallPath() 
                + "bin\\mysqladmin.exe"
                + "\""
                + " -u " 
                + this.getLogin()
                + " -p "
                + this.getPassword()
                + " shutdown";
        String copy = "copy \"" + dataDir + "\" \"" + this.getBackupColdPath() + "\" ";
        String start = "net start " + nameService;
        
        
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = "saveColdMySQL.cmd";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            /*
             * Ecriture dans le fichier
             */        
            os.write(command.getBytes());
            os.write("\r\n".getBytes());
            os.write(copy.getBytes());
            os.write("\r\n".getBytes());
            os.write(start.getBytes());
            os.write("\r\n".getBytes());
            os.write("pause".getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete(); 
    }
    
    public List<String> getAllDataBases() {
        String query = "SHOW databases;";
        List<String> allDatabases = new ArrayList<String>(); 
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                allDatabases.add(res.getString(1));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return allDatabases;
    }

    /**
     *
     * REQUETES DEVELOPPEUR
     */
    
    public List<ObjectMySql> getAllObjectMySqlForCurrentUser() {
        allObjectMySqlForCurrentUser = new ArrayList<ObjectMySql>();
        String currentConnectionName = this.getCompteBean().getCurrent().getName();
        
        //Tables
        String query = "SELECT table_name "
                + "FROM information_schema.tables "
                + "WHERE TABLE_SCHEMA = '"
                + currentConnectionName
                + "' "
                + "AND TABLE_TYPE='BASE TABLE';";
        List<String> tableList = new ArrayList<String>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {                
                tableList.add(res.getString(1));
            }
            ObjectMySql objectMySql = new ObjectMySql();
            objectMySql.setType("Table");
            objectMySql.setListObject(tableList);
            objectMySql.setCount(tableList.size());
            allObjectMySqlForCurrentUser.add(objectMySql);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       
        //Vues
        query = "SELECT table_name "
                + "FROM information_schema.tables "
                + "WHERE TABLE_SCHEMA = '"
                + currentConnectionName
                + "' "
                + "AND TABLE_TYPE='VIEW';";
        List<String> viewList = new ArrayList<String>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {                
                viewList.add(res.getString(1));
            }
            ObjectMySql objectMySql = new ObjectMySql();
            objectMySql.setType("View");
            objectMySql.setListObject(viewList);
            objectMySql.setCount(viewList.size());
            allObjectMySqlForCurrentUser.add(objectMySql);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        //Triggers
        query = "SELECT TRIGGER_NAME "
                + "FROM information_schema.triggers "
                + "WHERE trigger_schema='"
                + currentConnectionName
                + "';";
        List<String> triggerList = new ArrayList<String>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {                
                triggerList.add(res.getString(1));
            }
            ObjectMySql objectMySql = new ObjectMySql();
            objectMySql.setType("Trigger");
            objectMySql.setListObject(triggerList);
            objectMySql.setCount(triggerList.size());
            allObjectMySqlForCurrentUser.add(objectMySql);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        //Procedures
        query = "SELECT ROUTINE_NAME "
                + "FROM INFORMATION_SCHEMA.ROUTINES "
                + "WHERE ROUTINE_SCHEMA = '"
                + currentConnectionName
                + "' "
                + "AND ROUTINE_TYPE='PROCEDURE';";
        List<String> procedureList = new ArrayList<String>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {                
                procedureList.add(res.getString(1));
            }
            ObjectMySql objectMySql = new ObjectMySql();
            objectMySql.setType("Procedure");
            objectMySql.setListObject(procedureList);
            objectMySql.setCount(procedureList.size());
            allObjectMySqlForCurrentUser.add(objectMySql);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        //Fonctions
        query = "SELECT ROUTINE_NAME "
                + "FROM INFORMATION_SCHEMA.ROUTINES "
                + "WHERE ROUTINE_SCHEMA = '"
                + currentConnectionName
                + "' "
                + "AND ROUTINE_TYPE='FUNCTION';";
        List<String> fonctionList = new ArrayList<String>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {                
                fonctionList.add(res.getString(1));
            }
            ObjectMySql objectMySql = new ObjectMySql();
            objectMySql.setType("Fonction");
            objectMySql.setListObject(fonctionList);
            objectMySql.setCount(fonctionList.size());
            allObjectMySqlForCurrentUser.add(objectMySql);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        //Indexs
        query = "SELECT INDEX_NAME, TABLE_NAME "
                + "FROM INFORMATION_SCHEMA.STATISTICS "
                + "WHERE TABLE_SCHEMA = '"
                + currentConnectionName
                + "';";
        List<String> indexList = new ArrayList<String>();
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {                
                indexList.add(res.getString(1) + " (Table : " + res.getString(2) + ")");
            }
            ObjectMySql objectMySql = new ObjectMySql();
            objectMySql.setType("Index");
            objectMySql.setListObject(indexList);
            objectMySql.setCount(indexList.size());
            allObjectMySqlForCurrentUser.add(objectMySql);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }        
        return allObjectMySqlForCurrentUser;        
    }
    
    public List<TableMySql> getAllTableMySqlForCurrentUser() {
        allTableMySqlForCurrentUser = new ArrayList<TableMySql>();
        String query = "SELECT table_name, table_rows, "
                + "engine, avg_row_length, "
                + "data_length/1024, max_data_length, "
                + "index_length, create_time "
                + "FROM information_schema.tables "
                + "WHERE TABLE_SCHEMA = '"
                + this.getCompteBean().getCurrent().getName()
                + "' "
                + "AND TABLE_TYPE ='BASE TABLE';";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                TableMySql tableMySql = new TableMySql();
                String tableName = res.getString(1);
                tableMySql.setName(tableName);
                tableMySql.setTableRows(res.getInt(2));
                tableMySql.setEngine(res.getString(3));
                tableMySql.setAvgRowLength(res.getInt(4));
                tableMySql.setDataLength(res.getInt(5));    //Taille en Ko
                tableMySql.setMaxDataLength(res.getInt(6));
                tableMySql.setIndexLength(res.getInt(7));
                tableMySql.setCreateTime(res.getString(8));
                tableMySql.setIndexes(this.getAllIndexMySqlByTable(tableName));
                tableMySql.setCommand(this.getCommandByTable(tableName));
                allTableMySqlForCurrentUser.add(tableMySql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allTableMySqlForCurrentUser;
    }

    public String getCommandByTable(String table) {
        String command = "";
        String query = "SHOW CREATE TABLE "
                + table
                + ";";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                command += res.getString(2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return command;
    }

    public void saveCommandTable() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentTable().getName() + "_TABLE.txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            os.write(this.getCurrentTable().getCommand().getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public List<IndexMySql> getAllIndexMySqlByTable(String table) {
        List<IndexMySql> allIndexMySqlByTable = new ArrayList<IndexMySql>();
        String query = "SELECT index_name, column_name, index_type "
                + "FROM INFORMATION_SCHEMA.STATISTICS "
                + "WHERE TABLE_SCHEMA = '"
                + this.getCompteBean().getCurrent().getName()
                + "' "
                + "AND table_name='"
                + table
                + "';";

        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                IndexMySql indexMySql = new IndexMySql();
                indexMySql.setName(res.getString(1));
                indexMySql.setColumnName(res.getString(2));
                indexMySql.setType(res.getString(3));
                allIndexMySqlByTable.add(indexMySql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return allIndexMySqlByTable;
    }

    public List<ViewMySql> getAllViewMySqlForCurrentUser() {
        allViewMySqlForCurrentUser = new ArrayList<ViewMySql>();
        String query = "SELECT table_name, view_definition "
                + "FROM information_schema.views "
                + "WHERE TABLE_SCHEMA = '"
                + this.getCompteBean().getCurrent().getName()
                + "';";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                ViewMySql viewMySql = new ViewMySql();
                String viewName = res.getString(1);
                viewMySql.setName(viewName);
                viewMySql.setCommand(this.getCommandViewByView(viewName));
                allViewMySqlForCurrentUser.add(viewMySql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }

        return allViewMySqlForCurrentUser;
    }

    public String getCommandViewByView(String view) {
        String commandView = "";
        String query = "SHOW CREATE VIEW "
                + view
                + ";";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                commandView += res.getString(2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return commandView;
    }

    public void saveCommandView() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentView().getName() + "_VIEW.txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            os.write(this.getCurrentView().getCommand().getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public ViewMySql getCurrentView() {
        return currentView;
    }

    public void setCurrentView(ViewMySql currentView) {
        this.currentView = currentView;
    }

    public List<MethodeMySql> getAllMethodeMySqlForCurrentUser() {
        allMethodeMySqlForCurrentUser = new ArrayList<MethodeMySql>();
        String query = "SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER "
                + "FROM INFORMATION_SCHEMA.ROUTINES "
                + "WHERE ROUTINE_SCHEMA = '"
                + this.getCompteBean().getCurrent().getName()
                + "';";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                MethodeMySql methodeMySql = new MethodeMySql();
                String name = res.getString(1);
                String type = res.getString(2);
                methodeMySql.setName(name);
                methodeMySql.setType(type);
                methodeMySql.setDefiner(res.getString(3));
                methodeMySql.setContent(this.getContentByTypeMethode(name, type));
                allMethodeMySqlForCurrentUser.add(methodeMySql);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allMethodeMySqlForCurrentUser;
    }

    public String getContentByTypeMethode(String methodeName, String methodeType) {
        String content = "";
        String query = "SHOW CREATE "
                + methodeType
                + " "
                + methodeName
                + ";";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                content += res.getString(3);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return content;
    }

    public void saveContentMethode() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentMethode().getName() + "_" + this.getCurrentMethode().getType() + ".txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            os.write(this.getCurrentMethode().getContent().getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public List<TriggerMySql> getAllTriggerMySqlForCurrentUser() {
        allTriggerMySqlForCurrentUser = new ArrayList<TriggerMySql>();
        String query = "SHOW triggers;";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                TriggerMySql trigger = new TriggerMySql();
                String name = res.getString(1);
                trigger.setName(name);
                trigger.setTableName(res.getString(3));
                trigger.setCommand(this.getCommandByTrigger(name));
                allTriggerMySqlForCurrentUser.add(trigger);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allTriggerMySqlForCurrentUser;
    }

    public String getCommandByTrigger(String triggerName) {
        String command = "";
        String query = "SHOW CREATE TRIGGER "
                + triggerName
                + ";";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                command += res.getString(3);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return command;
    }

    public void saveCommandTrigger() {
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = this.getCurrentTrigger().getName() + "_TRIGGER.txt";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            os.write(this.getCurrentTrigger().getCommand().getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();
    }

    public MethodeMySql getCurrentMethode() {
        return currentMethode;
    }

    public void setCurrentMethode(MethodeMySql currentMethode) {
        this.currentMethode = currentMethode;
    }

    public TableMySql getCurrentTable() {
        return currentTable;
    }

    public void setCurrentTable(TableMySql currentTable) {
        this.currentTable = currentTable;
    }

    public TriggerMySql getCurrentTrigger() {
        return currentTrigger;
    }

    public void setCurrentTrigger(TriggerMySql currentTrigger) {
        this.currentTrigger = currentTrigger;
    }

    public String getMyQuery() {
        return myQuery;
    }

    public void setMyQuery(String myQuery) {
        this.myQuery = myQuery;
    }

    public void createExecutionPlan() {
        this.getExecutionPlan();
    }

    public List<ExecutionPlanMySql> getExecutionPlan() {
        List<ExecutionPlanMySql> executionPlanList = new ArrayList<ExecutionPlanMySql>();
        if (this.getMyQuery() != null && !this.getMyQuery().isEmpty()) {
            /*
             * Test si ; présent ou non
             */
            if(!this.getMyQuery().endsWith(";")) {
                this.myQuery += ";";
            }
            String query = "EXPLAIN "
                    + this.getMyQuery();
            try {
                Statement stm = getConnection().createStatement();
                ResultSet res = stm.executeQuery(query);
                while (res.next()) {
                    ExecutionPlanMySql executionPlan = new ExecutionPlanMySql();
                    executionPlan.setId(res.getInt(1));
                    executionPlan.setSelectType(res.getString(2));
                    executionPlan.setTable(res.getString(3));
                    executionPlan.setType(res.getString(4));
                    executionPlan.setPossibleKeys(res.getString(5));
                    executionPlan.setKey(res.getString(6));
                    executionPlan.setKeyLength(res.getInt(7));
                    executionPlan.setRef(res.getString(8));
                    executionPlan.setRows(res.getInt(9));
                    executionPlan.setExtra(res.getString(10));
                    executionPlanList.add(executionPlan);
                }
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return executionPlanList;
    }

    public void saveExecutionPlan() throws IOException {
        List<ExecutionPlanMySql> executionPlans = this.getExecutionPlan();

        int rowCount = 0;
        int colCount = 0;
        String filename = "ExecutionPlanExport.xls";

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(filename);
        HSSFHeader header = sheet.getHeader();
        header.setCenter(filename);
        //CreationHelper createHelper = wb.getCreationHelper();

        String contentType = "application/vnd.ms-excel";
        FacesContext fc = FacesContext.getCurrentInstance();
        HttpServletResponse response = (HttpServletResponse) fc.getExternalContext().getResponse();
        response.setHeader("Content-disposition", "attachment; filename=" + filename);
        response.setContentType(contentType);
        ServletOutputStream out = response.getOutputStream();

        try {
            Row row = sheet.createRow((short) rowCount);
            row.createCell(0).setCellValue("Requête");
            row.createCell(1).setCellValue(this.getMyQuery());
            rowCount++;
            row = sheet.createRow((short) rowCount);
            row.createCell(0).setCellValue("Id");
            row.createCell(1).setCellValue("SelectType");
            row.createCell(2).setCellValue("Table");
            row.createCell(3).setCellValue("Type");
            row.createCell(4).setCellValue("Possible Keys");
            row.createCell(5).setCellValue("Key");
            row.createCell(6).setCellValue("Key length");
            row.createCell(7).setCellValue("Ref");
            row.createCell(8).setCellValue("Rows");
            row.createCell(9).setCellValue("Extra");
            rowCount++;
            for (ExecutionPlanMySql exec : executionPlans) {
                row = sheet.createRow((short) rowCount);
                row.createCell(0).setCellValue(exec.getId());
                row.createCell(1).setCellValue(exec.getSelectType());
                row.createCell(2).setCellValue(exec.getTable());
                row.createCell(3).setCellValue(exec.getType());
                row.createCell(4).setCellValue(exec.getPossibleKeys());
                row.createCell(5).setCellValue(exec.getKey());
                row.createCell(6).setCellValue(exec.getKeyLength());
                row.createCell(7).setCellValue(exec.getRef());
                row.createCell(8).setCellValue(exec.getRows());
                row.createCell(9).setCellValue(exec.getExtra());
                rowCount++;
            }
        } catch (Exception e) {
            System.out.println("Error : " + e);
        }
        colCount = 9;
        int i = 0;
        while (i <= colCount) {
            sheet.setColumnWidth(i, 8000);
            i++;
        }

        wb.write(out);
        out.close();
        fc.responseComplete();

    }

    public LoginBean getLoginBean() {
        return loginBean;
    }

    public void setLoginBean(LoginBean loginBean) {
        this.loginBean = loginBean;
    }
    
    /*
     * Procédure qui exécute le script de sauvegarde à chaud
     */
    public void executeHotBackup() {

        /*
         * Test s'il n'y a pas de backslash à la fin du chemin
         */
        if (System.getProperty("file.separator").charAt(0) != (this.getBackupHotPath().charAt(this.getBackupHotPath().length() - 1))) {
            String backslash = System.getProperty("file.separator");
            backupHotPath += backslash;
        }

        if (System.getProperty("file.separator").charAt(0) != (this.getInstallPath().charAt(this.getInstallPath().length() - 1))) {
            String backslash = System.getProperty("file.separator");
            installPath += backslash;
        }

        try {
            PrintWriter ecrivain = new PrintWriter(new BufferedWriter(new FileWriter(this.getBackupHotPath() + "backupMySql.sql")));
            String command = "\""
                    + this.getInstallPath()
                    + "bin\\mysqldump"
                    + "\""
                    + " -u "
                    + this.getLogin()
                    + " -p"
                    + this.getPassword()
                    + " "
                    + this.getCompteBean().getCurrent().getName()
                    + " -h "
                    + this.getCompteBean().getCurrent().getServer();

            try {
                Runtime r = Runtime.getRuntime();
                Process p = r.exec(command);
                BufferedReader in = new BufferedReader(
                        new InputStreamReader(p.getInputStream()));
                String line = null;
                while ((line = in.readLine()) != null) {
                    ecrivain.write(line);
                    ecrivain.write("\n");
                }
                p.waitFor();//si l'application doit attendre a ce que ce process fini
            } catch (Exception e) {
                e.printStackTrace();
            }
            ecrivain.close();
        } catch (IOException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /*
     * Procédure qui sauvegarde le script de sauvegarde à chaud
     */
    public void saveBackupHotScript() {
        if(System.getProperty("file.separator").charAt(0) != (this.getBackupHotPath().charAt(this.getBackupHotPath().length()-1))) {
            String backslash= System.getProperty("file.separator");
            backupHotPath += backslash;            
        }
        
        if(System.getProperty("file.separator").charAt(0) != (this.getInstallPath().charAt(this.getInstallPath().length()-1))) {
            String backslash= System.getProperty("file.separator");
            installPath += backslash;            
        }
        
        String command = "\""
                + this.getInstallPath()
                + "bin\\mysqldump"
                + "\""
                + " -u "
                + this.getLogin()
                + " -p"
                + this.getPassword()
                + " "
                + this.getCompteBean().getCurrent().getName()
                + " -h "
                + this.getCompteBean().getCurrent().getServer()
                + " > "
                + this.getBackupHotPath()
                + "backupMySql.sql";
        
        FacesContext context = FacesContext.getCurrentInstance();
        try {
            String filename = "saveHotMySQL.cmd";
            HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            response.setContentType("application/octet-stream"); //fill in contentType
            OutputStream os = response.getOutputStream();
            /*
             * Ecriture dans le fichier
             */        
            os.write(command.getBytes());
            os.write("\r\n".getBytes());
            os.write("pause".getBytes());
            os.flush();
            os.close();
        } catch (Exception x) {
            x.printStackTrace();
        }
        context.responseComplete();        
    }

    public String getBackupHotPath() {
        return backupHotPath;
    }

    public void setBackupHotPath(String backupHotPath) {
        this.backupHotPath = backupHotPath;
    }

    public String getInstallPath() {
        String query = "SHOW variables WHERE variable_name='basedir';";
        try {
            Statement stm = getConnection().createStatement();
            ResultSet res = stm.executeQuery(query);
            while (res.next()) {
                installPath = res.getString(2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        installPath = installPath.replace("/", "\\");
        return installPath;
    }

    public void setInstallPath(String installPath) {
        this.installPath = installPath;
    }

    public String getBackupColdPath() {
        return backupColdPath;
    }

    public void setBackupColdPath(String backupColdPath) {
        this.backupColdPath = backupColdPath;
    }

    public String getNameService() {
        return nameService;
    }

    public void setNameService(String nameService) {
        this.nameService = nameService;
    }
}
